package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTMainTenanceDto;
import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.MaintenanceDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by ding on 2017/3/3.
 */
public class MaintenanceRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<DTMainTenanceDto> getMaintenance4DT(String text, Integer isRead, DTRequestDto dtRequestDto, Integer roadSignId) throws Exception {
        String Status = dtRequestDto.getSearch();
        String sql = "SELECT \n" +
                "MaintenanceId AS maintenanceId,RoadSignId AS roadSignId,MaintenanceReason AS maintenanceReason,MaintenanceLevel AS maintenanceLevel,MaintenanceType AS maintenanceType,MaintenancePic AS maintenancePic,MaintenanceContent AS maintenanceContent,MaintenanceFeedback AS maintenanceFeedback,MaintenanceSignStatus AS maintenanceSignStatus,MaintenancePeopleId AS maintenancePeopleId,MaintenanceCreateTime AS maintenanceCreateTime,CompanyCheck AS companyCheck,MainCheck AS mainCheck,MainPerson AS mainPerson,CompanyPerson AS companyPerson,MaintenanceStatus AS maintenanceStatus,MaintenanceTime AS maintenanceTime,CompanyPersonName AS companyPersonName,MainPersonName AS mainPersonName,MaintenancePeopleName AS maintenancePeopleName\n" +
                "FROM tb_maintenance where CompanyCheck=1 AND MainCheck = 1 AND RoadSignId=:roadSignId order by SignFaultTime desc";
        if (!text.equals("")) {
            sql += " AND CONCAT(mainPersonName,maintenancePeopleName,companyPersonName) LIKE '%" + text + "%'  \n";
        }
        if (isRead != -1) {
            sql += " AND MaintenanceStatus like '%" + isRead + "%'";
        }
        sql += " ORDER BY MaintenanceStatus";
        Query query = em.createNativeQuery(sql);
        query.setParameter("roadSignId", roadSignId);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTMainTenanceDto.class));
        List<DTMainTenanceDto> orgDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return orgDtos;
    }


    public String getmainPersonNameById(Integer mainPerson) throws Exception {
        String sql = "SELECT username\n" +
                "FROM tb_user\n" +
                "WHERE UserId=:mainPerson";
        Query query = em.createNativeQuery(sql);
        query.setParameter("mainPerson", mainPerson);
        return (String) query.getSingleResult();
    }

    public String getcompanyPersonNameById(Integer companyPerson) throws Exception {
        String sql = "SELECT username\n" +
                "FROM tb_user\n" +
                "WHERE UserId=:companyPerson";
        Query query = em.createNativeQuery(sql);
        query.setParameter("companyPerson", companyPerson);
        return (String) query.getSingleResult();
    }

    public String getmaintenancePeopleNameById(Integer maintenancePeopleId) throws Exception {
        String sql = "SELECT username\n" +
                "FROM tb_user\n" +
                "WHERE UserId=:maintenancePeopleId";
        Query query = em.createNativeQuery(sql);
        query.setParameter("maintenancePeopleId", maintenancePeopleId);
        return (String) query.getSingleResult();
    }

    public Integer updateMaintenanceById(Integer maintenanceId, String maintenanceTime, String maintenanceContent, String maintenancePic, Integer maintenanceSignStatus, String maintenanceFeedback) throws Exception {
        String sql = "UPDATE tb_maintenance\n" +
                "SET maintenanceTime =:maintenanceTime,maintenanceContent=:maintenanceContent,maintenancePic =:maintenancePic,MaintenanceStatus=:maintenanceSignStatus,MaintenanceSignStatus =:maintenanceSignStatus,MaintenanceFeedback =:maintenanceFeedback \n" +
                "where maintenanceId =:maintenanceId";
        Query query = em.createNativeQuery(sql);
        query.setParameter("maintenanceId", maintenanceId);
        query.setParameter("maintenanceTime", maintenanceTime);
        query.setParameter("maintenanceContent", maintenanceContent);
        query.setParameter("maintenancePic", maintenancePic);
        query.setParameter("maintenanceSignStatus", maintenanceSignStatus);
        query.setParameter("maintenanceFeedback", maintenanceFeedback);
        return query.executeUpdate();
    }

    public Integer saveFeedbackInfo(Integer maintenanceId, String maintenanceFeedback) throws Exception {
        String sql = "UPDATE tb_maintenance\n" +
                "SET maintenanceFeedback =:maintenanceFeedback,MaintenanceStatus=3 \n" +
                "where maintenanceId =:maintenanceId";
        Query query = em.createNativeQuery(sql);
        query.setParameter("maintenanceId", maintenanceId);
        query.setParameter("maintenanceFeedback", maintenanceFeedback);
        return query.executeUpdate();
    }

    public Integer getMaintenanceCount4DT(String text, Integer isRead, Integer roadSignId) throws Exception {
        String sql = "SELECT COUNT(*) \n" +
                "FROM tb_maintenance where CompanyCheck=1 AND MainCheck = 1 AND RoadSignId=:roadSignId ";
        if (!text.equals("")) {
            sql += " AND CONCAT(mainPersonName,maintenancePeopleName,companyPersonName) LIKE '%" + text + "%'  \n";
        }
        if (isRead != -1) {
            sql += " AND MaintenanceStatus like '%" + isRead + "%'";
        }
        Query query = em.createNativeQuery(sql);
        query.setParameter("roadSignId", roadSignId);
        query.unwrap(NativeQuery.class);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<MaintenanceDto> getMaintenance4Disposal(DTRequestDto dtRequestDto, Integer actorId, Integer userId) throws Exception {
        String sql = "select * from tb_maintenance where concat(mainPersonName,maintenancePeopleName,companyPersonName)" +
                " like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3)
            sql += " and CompanyPerson=" + userId + "";
        else if (actorId == 5)
            sql += " and MaintenancePeopleId=" + userId + "";
        sql += " order by MaintenanceCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return maintenanceDtos;
    }

    public Integer getMaintenanceCount4Disposal(DTRequestDto dtRequestDto, Integer actorId, Integer userId) throws Exception {
        String sql = "select maintenanceId from tb_maintenance where  concat(mainPersonName,maintenancePeopleName,companyPersonName)" +
                " like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3)
            sql += " and CompanyPerson=" + userId + "";
        else if (actorId == 5)
            sql += " and MaintenancePeopleId=" + userId + "";
        Query query = em.createNativeQuery(sql);
        List<Integer> signfaultDtos = query.getResultList();
        return signfaultDtos.size();
    }

    public List<MaintenanceDto> getMaintenance4Order(DTRequestDto dtRequestDto, Integer actorId, Integer userId) throws Exception {
        String sql = "select * from tb_maintenance where CompanyCheck=1 and concat(mainPersonName,maintenancePeopleName,companyPersonName)" +
                " like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 7)
            sql += " and MainPerson=" + userId + "";
        sql += " order by MaintenanceCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return maintenanceDtos;
    }

    public Integer getMaintenanceCount4Order(DTRequestDto dtRequestDto, Integer actorId, Integer userId) throws Exception {
        String sql = "select MaintenanceId from tb_maintenance where CompanyCheck=1 and concat(mainPersonName,maintenancePeopleName,companyPersonName)" +
                " like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 7)
            sql += " and MainPerson=" + userId + "";
        Query query = em.createNativeQuery(sql);
        List<Integer> maintenanceDtos = query.getResultList();
        return maintenanceDtos.size();
    }

    public List<MaintenanceDto> getMaintenanceByUserId(DTRequestDto dtRequestDto, Integer actorId, Integer userId) throws Exception {
        String sql = "select * from tb_maintenance where concat(mainPersonName,maintenancePeopleName,companyPersonName)" +
                " like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3)
            sql += "and CompanyPerson=" + userId + "";
        if (actorId == 7)
            sql += "and MainPerson=" + userId + "";
        if (actorId == 5)
            sql += "and MaintenancePeopleId=" + userId + "";
        sql += " order by MaintenanceCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return maintenanceDtos;
    }

    public Integer getMaintenanceCountByUserId(DTRequestDto dtRequestDto, Integer actorId, Integer userId) throws Exception {
        String sql = "select MaintenanceId from tb_maintenance where concat(mainPersonName,maintenancePeopleName,companyPersonName)" +
                " like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3)
            sql += "and CompanyPerson=" + userId + "";
        if (actorId == 7)
            sql += "and MainPerson=" + userId + "";
        if (actorId == 5)
            sql += "and MaintenancePeopleId=" + userId + "";
        Query query = em.createNativeQuery(sql);
        List<Integer> maintenanceDtos = query.getResultList();
        return maintenanceDtos.size();

    }


    public List<MaintenanceDto> getFaultHandle4DT(DTRequestDto dtRequestDto,
                                                  Integer orgId,
                                                  Integer maintenanceStatus,
                                                  String actorIds) throws Exception {
        String sql = "select u.userName as creatorName, mt.maintenanceId, mt.roadSignId, mt.maintenanceReason, mt.maintenanceLevel, " +
                "mt.maintenanceType, mt.maintenancePic, mt.maintenanceContent, mt.maintenanceFeedback, " +
                "mt.maintenanceExplain, mt.maintenanceSignStatus, mt.maintenanceTime, mt.maintenanceStatus, " +
                "mt.maintenancePeopleId, mt.maintenanceCreateTime, mt.signFaultPic, rs.roadSignPic,mt.repairAmount, " +
                "rp.province, rp.city, rp.area, rp.roadName, rp.startRoad, rp.endRoad, rp.longitude, rp.latitude " +
                "from tb_maintenance mt, tb_roadpole rp, tb_roadsign rs,tb_user u,tb_signfault s " +
                "where mt.roadSignId = rs.roadSignId and rp.roadPoleId = rs.roadPoleId and u.userId= s.SignFaultPeopleId and mt.signFaultId=s.signFaultId " +
                "and concat(rp.roadName, rp.startRoad, rp.endRoad) like '%" + dtRequestDto.getSearch() + "%' ";
        if (maintenanceStatus != 3)
            sql += "and mt.maintenanceStatus = " + maintenanceStatus + " ";
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += "and rp.orgId = " + orgId + " ";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += "and rp.mainOrgId = " + orgId + " ";
        }
        sql += "order by mt.maintenanceCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getCount4DT(String search,
                               Integer orgId,
                               Integer maintenanceStatus,
                               String actorIds) throws Exception {
        String sql = "select count(*) " +
                "from tb_maintenance mt, tb_roadpole rp, tb_roadsign rs " +
                "where mt.roadSignId = rs.roadSignId and rp.roadPoleId = rs.roadPoleId " +
                "and concat(rp.roadName, rp.startRoad, rp.endRoad) like '%" + search + "%' ";
        if (maintenanceStatus != 3)
            sql += "and mt.maintenanceStatus = " + maintenanceStatus + " ";
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += "and rp.orgId = " + orgId + " ";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += "and rp.mainOrgId = " + orgId + " ";
        }
        sql += "order by mt.maintenanceCreateTime desc";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<MaintenanceDto> getFaultHandle(Integer orgId,
                                               Integer maintenanceStatus,
                                               String actorIds,
                                               Integer mCount,
                                               Integer mySize) throws Exception {
        String sql = "select mt.maintenanceId, mt.roadSignId, mt.maintenanceReason, mt.maintenanceLevel, " +
                "mt.maintenanceType, mt.maintenancePic, mt.maintenanceContent, mt.maintenanceFeedback, " +
                "mt.maintenanceExplain, mt.maintenanceSignStatus, mt.maintenanceTime, mt.maintenanceStatus, " +
                "mt.maintenancePeopleId, mt.maintenanceCreateTime, mt.signFaultPic, " +
                "rp.province, rp.city, rp.area, rp.roadName, rp.startRoad, rp.endRoad, rp.longitude, rp.latitude " +
                "from tb_maintenance mt, tb_roadpole rp, tb_roadsign rs " +
                "where mt.roadSignId = rs.roadSignId and rp.roadPoleId = rs.roadPoleId ";
        if (maintenanceStatus != 3)
            sql += "and mt.maintenanceStatus = " + maintenanceStatus + " ";
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += "and rp.orgId = " + orgId + " ";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += "and rp.mainOrgId = " + orgId + " ";
        }
        sql += "order by mt.maintenanceCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        return query.setFirstResult((mCount - 1) * mySize).setMaxResults(mySize).getResultList();
    }

    public Integer getMaintenanceStatusByRoadSignId(Integer roadSignId) throws Exception {
        String sql = "select MaintenanceStatus from tb_maintenance where RoadSignId=" + roadSignId + " order by" +
                " MaintenanceCreateTime desc LIMIT 1";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

}
